<?php
class audit_log extends Model{
	public static function getTableName(){
		return 'audit_log';
	}

	public function getResultList($findRelation, $date, $start, $length)
	{
		$find	=	$findRelation['item'];

		#   subSql /*{{{*/
		$inItemType = array(
			'3'	=> 'cast',
			'7'	=> 'chapter',
			'14' => 'episode',
			'23' => 'image',
			'39' => 'programme',
			'52' => 'video',
			'24' => 'info_address',
			'25' => 'info_award',
			'26' => 'info_biography',
			'27' => 'info_general_information',
			'53' => 'info_pageant',
			'28' => 'info_product',
			'29' => 'info_q_a',
			'30' => 'info_recipe',
			'31' => 'info_song',
		);
		$inItemTypeIdString = '"'. implode('", "', array_keys($inItemType)). '"';

		$subSql = '
			SELECT A.id, A.action, I.system_label AS itemType, A.item_id, A.modified_at, A.status
			FROM audit_log A
			INNER JOIN (
				SELECT MAX(B.id) AS id
				FROM audit_log B 
				WHERE B.item_type_id in ('. $inItemTypeIdString. ')
					AND B.modified_by = "'. CommonUtils::get_user() .'"
					AND B.modified_at BETWEEN "'.$date.' 00:00:00" AND "'.$date.' 23:59:59"
					AND B.item_id > 0				
				GROUP BY B.item_type_id, B.item_id
			) AS AAA ON A.id = AAA.id
			LEFT JOIN item_type AS I ON (A.item_type_id = I.id)';
		/*}}}*/

		# sql /*{{{*/
		$sql=   '
			SELECT
				id,
				action,
				itemType,
				item_id,
				replace(modified_at, " ", "<br />")		AS	modified_at,
				status								  AS  record_status
			FROM
			(
				'. $subSql. '
			)	AS	findApi
			ORDER BY modified_at DESC, itemType ASC
			';
		/*}}}*/

		$dataSql	=	$sql. ' LIMIT '. $start. ', '. $length;
		$totalSql	=	'SELECT count(*) AS num FROM ('. $subSql. ') AS tmp';

		$result	=	Model::$dbo['r']->findBySql(__class__,$dataSql);
		$retAll	=	Model::$dbo['r']->findBySql(__class__,$totalSql);

		$list	=	array();
		$list['page']	=	ceil($start / $length) + 1;
		$list['total']	=	ceil($retAll[0]->num / $length);
		$list['records']=	$retAll[0]->num;

		foreach	($result	as	$gridData)
		{
			$tmp			=	array();
			$tmp['id']		=	$gridData->id;
			$tmp['cell']	=	array();

			$originalStatus =   $gridData->record_status;
			$itemType	   =   $gridData->itemType;
			$itemID		 =   $gridData->item_id;
			$tmpProgramme   =   $this->getProgrammeTitle($itemType, $itemID);
			$findTable	  =   isset($find['table'][$itemType])	?   $find['table'][$itemType]   :   $itemType;
			$findColumnID   =   isset($find['columnID'][$itemType]) ?   $find['columnID'][$itemType]:   'id';
			$itemInformation=   $this->getLogItemTitle($findTable, $find['itemTitle'][$itemType], $findColumnID, $itemID);

			# if belong info, the name must get from AC::$table2Name
			$itemType	   =   (isset(AC::$table2Name[$findTable]))?   AC::$table2Name[$findTable] :   ucwords($itemType);

			$tmp['cell'][]  =   isset(AC::$log_type[$gridData->action]) ?
								AC::$log_type[$gridData->action]	:   $gridData->action;
			$tmp['cell'][]  =   $gridData->item_id;
			$tmp['cell'][]  =   $itemType;

			$tmp['cell'][]  =   $tmpProgramme;

			$tmp['cell'][]  =   $itemInformation['itemTitle'];
			$tmp['cell'][]  =   isset(AC::$op_status[$gridData->record_status]) ?
								AC::$op_status[$gridData->record_status]	:   $gridData->record_status;

			$tmp['cell'][]  =   $gridData->modified_at;

			if (empty($itemInformation['vid'])) $id_itemID  =   'id='. $itemID;
			else	$id_itemID  =   'vid='. $itemInformation['vid'];

			# check info or other!
			$addType		=   '&step=2';
			if (isset(AC::$name2Type[$findTable]))
			{
				$itemType   =   AC::$name2Type[$findTable];
				$addType	=   '&type='. strtolower($itemType);
				$itemType   =   'info';
			}
			$itemType	   =   strtolower($itemType);

			$tmp['cell'][]  =   '<a title="View" href="get_'. $itemType. '.html?from=task&act=view&'. $id_itemID. $addType. '" class="btn view ui-state-default ui-corner-all"><span class="ui-icon ui-icon-image"/>View</a>';

			$list['rows'][]	=	$tmp;
		}
		return	$list;
	}

	public function getLogItemTitle($table, $itemTitle, $columnID, $id)
	{
		$sql=   '
			SELECT '.$itemTitle. ' AS  itemTitle
			FROM
				`'. $table. '`
			WHERE
				'. $columnID .' = "'. $id. '"
			';
		$ret['itemTitle'] = array();
		$result = Model::selectBySql($sql);
		foreach($result as $row){
			if (isset($row['info_cn_title'])) {
				$ret['itemTitle'][] = htmlentities($row['info_cn_title'], ENT_QUOTES, 'UTF-8');
			}
			
			$ret['itemTitle'][] = htmlentities($row['itemTitle'],ENT_QUOTES,'UTF-8');
		}
		$ret['itemTitle'] = implode('<br />',$ret['itemTitle']);
		
		#$result			 =	Model::$dbo['r']->findBySql(__class__,$sql);
		
		#$ret['itemTitle']   =   empty($result[0]->itemTitle) ?   NULL	:   $result[0]->itemTitle;
		
		if ('chapter-language'  ==  $table)
		{
			$sql	=   'SELECT * FROM  chapter WHERE id = "'. $id. '"';
			$chapter=   Model::$dbo['r']->findBySql(__class__, $sql);
			$ret['vid'] =   empty($chapter[0]->video_id)?   NULL	:   $chapter[0]->video_id;
		}
		return  $ret;
	}

	public function getProgrammeTitle($table, $itemId)
	{
		$isInfo	=   isset(AC::$name2Type[$table])  ?  true   :  false  ;

		if('programme' == $table)
		{
			$field = 'id'; 
		}
		else
		{
			$field = 'programme_id';
		}

		if($isInfo)
		{
			$sql =  '
				SELECT 
					GROUP_CONCAT(title SEPARATOR "\n\t") AS  programmeTitle 
				FROM 
					`programme-language` 
				WHERE 
					programme_id	=  ( 
						SELECT  
							programme_id 
						FROM 
							`episode` 
						WHERE 
							id  =  ( 
								SELECT  
									episode_id 
								FROM 
								   '. $table  .' 
								WHERE 
									id = "'. $itemId .'"
							)
					) 
			'; 
		}
		else if ('chapter' == $table )
		{
			$sql =  '
				SELECT 
					GROUP_CONCAT(title SEPARATOR "\n\t") AS  programmeTitle 
				FROM 
					`programme-language` 
				WHERE 
					programme_id	=  
					( 
						SELECT  
							programme_id 
						FROM 
							`video` 
						WHERE 
							id  =  
							( 
								SELECT  
									video_id 
								FROM 
									chapter
								WHERE 
									id = "'. $itemId .'"
							)
					) 
			'; 
		}
		else 
		{
			$sql =  '
			SELECT 
				GROUP_CONCAT(title SEPARATOR "\n\t")  AS  programmeTitle
			FROM 
				`programme-language` 
			WHERE 
				programme_id   =   (
					SELECT ' . $field .' 
					FROM 
						`'. $table .'` 
					WHERE 
						id = "'. $itemId .'"  )
			';  
		}
		#$result =   Model::$dbo['r']->findBySql(__class__,$sql);
		#$ret	=   empty($result[0]->programmeTitle)   ?   NULL	:   $result[0]->programmeTitle;
		#return $ret; 
		$result = Model::selectBySql($sql);
		if(isset($result[0]['programmeTitle'])){
			$programmeTitle	= explode("\n\t",$result[0]['programmeTitle']);
			$programmeTitle[0] = htmlentities($programmeTitle[0],ENT_QUOTES,'UTF-8');
			$programmeTitle[1] = isset($programmeTitle[1]) ? htmlentities($programmeTitle[1],ENT_QUOTES,'UTF-8') : '';
			return implode('<br />',$programmeTitle);
		}else{
			return NULL;
		}
	}


	public function getWorkDays($day = NULL)
	{
		if (empty($day))	$day	 =   date("Y-m-d");

		$sql=   '
			SELECT DISTINCT
				DATE_FORMAT(modified_at, "%Y-%m-%d")	AS  lastWorkDay
			FROM
				audit_log
			WHERE
				modified_at < "'. $day. '"
				AND modified_by = "'. CommonUtils::get_user() . '"
			ORDER BY lastWorkDay DESC
			LIMIT 1
			';
		$result =	Model::$dbo['r']->findBySql(__class__,$sql);
		$ret	=   empty($result[0]->lastWorkDay)  ?   NULL	:   $result[0]->lastWorkDay;
		return  $ret;
	}

}
?>
